全局(Global) 与本地(Local)索引的区别 |
您所在的位置:网站首页 › oracle 建索引 online › 全局(Global) 与本地(Local)索引的区别 |
一、定义说明 oracle数据库中,存在多种对象,最常见的表和索引,索引的目的是为了加快查询;oracle建议一个表>2g时,就建议进行分区,分区表的好处此处省略,分区表有维护操作,但是某些维护操作对整个表的索引维护造成巨大开销,因此本地索引就是解决分区表管理的问题。 非分区索引创建方式,create index index_name on table_name(column_name); 查询索引状态从dba_indexes->status列查询 全局分区索引创建,create index index_name on table_name(column_name) global partition by range(column_name) (partition p1 values less than (11104) tablespace system,partition p2 values less than (11100) tablespace users,partition p3 values less than (maxvalue) tablespace sysaux.) or partition by hash (id) partitions 8 online等; 查询索引状态dba_ind_partitions->STATUS列查询 查询分区索引类型,是全局or本地 DBA_PART_INDEXES ->LOCALITY 本地分区索引创建,create index index_name on table_name(column_name) local; 查询索引状态dba_ind_partitions->STATUS列查询
二、create/selet 说明 1) 非分区索引 SQL> create index ind_1 on range_part_tab(deal_date); Index created. SQL> select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_1'; no rows selected SQL> select index_name,STATUS,GLOBAL_STATS,TABLESPACE_NAME from dba_indexes where owner='SYS' and index_name='IND_1'; INDEX_NAME STATUS GLO TABLESPACE_NAME ------------------------------ -------- --- ------------------------------ IND_1 VALID YES SYSTEM STATUS 表示一个非分区索引是否VALID或UNUSABLE GLOBAL_STATUS 对于分区索引,指示是通过分析整个索引(YES)来收集统计信息,还是根据基础索引分区和子分区(NO)的统计信息进行估计2)全局分区索引 create index ind_3 on range_part_tab(area_code) global partition by range(area_code) (partition p1 values less than (11104) tablespace system,partition p2 values less than (11100) tablespace users,partition p3 values less than (maxvalue) tablespace sysaux) *ERROR at line 1:ORA-14037: partition bound of partition "P1" is too high 12:39:59 SYS@ceshi>select area_code,count(*) from range_part_tab group by area_code; AREA_CODE COUNT(*)---------- ---------- 599 11104 594 11074 596 11021 593 11113 595 10927 592 11214 598 11235 591 11169 597 11143 9 rows selected. 全局范围分区索引,第一个分区不能比所有的值都大 SYS@ceshi>create index ind_3 on range_part_tab(area_code) global partition by range(area_code) (partition p1 values less than (592) tablespace system,partition p2 values less than (11100) tablespace users,partition p3 values less than (maxvalue) tablespace sysaux); Index created. select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_3' INDEX_NAME PARTITION_ HIGH_VALUE STATUS TABLESPACE_NAME---------- ---------- ------------------------------ -------- ------------------------------IND_3 P1 592 USABLE SYSTEMIND_3 P2 11100 USABLE USERSIND_3 P3 MAXVALUE USABLE SYSAUX
PARTITION_NAME 索引的分区名称 HIGH_VALUE 索引分区的范围条件 STATUS 分区索引的状态 SYS@ceshi>select index_name,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,LOCALITY,DEF_TABLESPACE_NAME from DBA_PART_INDEXES where index_name='IND_3'; INDEX_NAME TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT LOCALI DEF_TABLESPACE_NAME---------- ------------------------------ --------- --------- --------------- ------ ------------------------------IND_3 RANGE_PART_TAB RANGE NONE 3 GLOBAL SYSTEM
hash全局分区索引,使用场景较多,例如索引块争用,且表为非分区表时 SYS@ceshi>create table a1 as select * from range_part_tab; SYS@ceshi>create index ind_4 on a1(id) global partition by hash (id) partitions 8 online; Index created. SYS@ceshi>alter index ind_4 parallel 1; Index altered. select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_4' INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME------------------------------ ------------------------------ ---------- -------- ------------------------------IND_4 SYS_P61 USABLE SYSTEMIND_4 SYS_P62 USABLE SYSTEMIND_4 SYS_P63 USABLE SYSTEMIND_4 SYS_P64 USABLE SYSTEMIND_4 SYS_P65 USABLE SYSTEMIND_4 SYS_P66 USABLE SYSTEMIND_4 SYS_P67 USABLE SYSTEMIND_4 SYS_P68 USABLE SYSTEM 8 rows selected. SYS@ceshi>select index_name,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,LOCALITY,DEF_TABLESPACE_NAME from DBA_PART_INDEXES where index_name='IND_4'; INDEX_NAME TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT LOCALI DEF_TABLESPACE_NAME------------------------------ ------------------------------ --------- --------- --------------- ------ ------------------------------IND_4 A1 HASH NONE 8 GLOBAL SYSTEM
3) 本地分区索引 SQL> create index ind_2 on range_part_tab(id) local; Index created. select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_2' INDEX_NAME PARTITION_ HIGH_VALUE STATUS TABLESPACE_NAME------------------ -----------------------------------------------------------------------------------------------------------------------IND_2 P1 TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P10 TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P11 TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P12 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P2 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P3 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P4 TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P5 TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P6 TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P7 TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P8 TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P9 TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEMIND_2 P_MAX MAXVALUE USABLE SYSTEM 13 rows selected. SYS@ceshi>select index_name,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,LOCALITY,DEF_TABLESPACE_NAME from DBA_PART_INDEXES where index_name='IND_2'; INDEX_NAME TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT LOCALI DEF_TABLESPACE_NAME---------- ------------------------------ --------- --------- --------------- ------ ------------------------------IND_2 RANGE_PART_TAB RANGE NONE 13 LOCAL
三、索引的重建 非分区索引: SQL> alter index ind_1 rebuild; 全局分区索引:
SQL> alter table range_part_tab move partition p1; Table altered. SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions where TABLE_OWNER='SYS' and TABLE_NAME='RANGE_PART_TAB'; TABLE_NAME PARTITION_ TABLESPACE_NAME ------------------------------ ---------- ------------------------------ RANGE_PART_TAB P1 SYSTEM RANGE_PART_TAB P10 SYSTEM RANGE_PART_TAB P11 SYSTEM SQL> select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_3'; INDEX_NAME PARTITION_ HIGH_VALUE STATUS TABLESPACE_NAME ---------- ---------- -------------------- -------- ------------------------------ IND_3 P1 592 USABLE SYSTEM IND_3 P2 11100 USABLE USERS IND_3 P3 MAXVALUE USABLE SYSAUX SQL> alter index ind_3 rebuild partition p1; Index altered.
全局分区索引,单个分区的move并不会导致全局分区索引的失效 本地分区索引 select index_name,PARTITION_NAME,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_2';INDEX_NAME PARTITION_ STATUS TABLESPACE_NAME---------- ---------- -------- ------------------------------IND_2 P1 USABLE SYSTEMIND_2 P10 USABLE SYSTEMIND_2 P11 USABLE SYSTEMIND_2 P12 USABLE SYSTEMSQL> alter table range_part_tab move partition p1; Table altered SQL> alter index ind_2 rebuild partition p1; Index altered.
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |